import os
import mysql.connector
import redis
from datetime import datetime
from typing import List, Dict, Optional, Tuple, Any
from loguru import logger

class DatabaseManager:
    _instance = None
    
    def __new__(cls):
        if cls._instance is None:
            cls._instance = super(DatabaseManager, cls).__new__(cls)
        return cls._instance
    
    def __init__(self):
        # 如果已经初始化过，直接返回
        if hasattr(self, 'initialized') and self.initialized:
            return
            
        # 初始化数据库
        self.db_config = {
            'host': os.getenv('DB_HOST', 'host.docker.internal'),
            'port': int(os.getenv('DB_PORT', '3307')),
            'user': os.getenv('DB_USER', 'root'),
            'password': os.getenv('DB_PASSWORD', 'root'),
            'database': os.getenv('DB_NAME', 'arbitrage'),
            'time_zone': '+8:00'  # 设置时区为 UTC+8
        }
        
        # Redis配置
        self.redis_config = {
            'host': os.getenv('REDIS_HOST', 'host.docker.internal'),
            'port': int(os.getenv('REDIS_PORT', '6379')),
            'db': int(os.getenv('REDIS_DB', '0')),
            'decode_responses': True  # 自动解码响应
        }
        
        # 初始化时就建立连接
        try:
            # MySQL连接
            self.conn = mysql.connector.connect(**self.db_config)
            self.cursor = self.conn.cursor(dictionary=True)
            logger.info("MySQL数据库连接已建立")
            
            # Redis连接
            self.redis_client = redis.Redis(**self.redis_config)
            # 测试Redis连接
            self.redis_client.ping()
            logger.info("Redis连接已建立")
            
        except Exception as e:
            logger.error(f"数据库连接失败: {str(e)}")
            raise
            
        self.initialized = True
        logger.info("数据库管理器初始化成功")
    
    def get_connection(self):
        """获取数据库连接"""
        # 检查连接是否有效
        if self.conn is None or not self.conn.is_connected():
            try:
                self.conn = mysql.connector.connect(**self.db_config)
                self.cursor = self.conn.cursor(dictionary=True)
                logger.info("重新建立数据库连接")
            except Exception as e:
                logger.error(f"重新建立数据库连接失败: {str(e)}")
                raise
        return self.conn, self.cursor
    
    def get_redis_client(self):
        """获取Redis客户端"""
        if not hasattr(self, 'redis_client') or not self.redis_client.ping():
            try:
                self.redis_client = redis.Redis(**self.redis_config)
                logger.info("重新建立Redis连接")
            except Exception as e:
                logger.error(f"重新建立Redis连接失败: {str(e)}")
                raise
        return self.redis_client
    
    def close(self):
        """关闭所有数据库连接"""
        # 关闭MySQL连接
        if self.conn and self.conn.is_connected():
            self.conn.close()
            self.conn = None
            self.cursor = None
            logger.info("MySQL连接已关闭")
            
        # 关闭Redis连接
        if hasattr(self, 'redis_client'):
            self.redis_client.close()
            self.redis_client = None
            logger.info("Redis连接已关闭")
    
    def get_all_tables(self) -> List[str]:
        """获取所有表名"""
        try:
            self.cursor.execute("SHOW TABLES")
            return [row[0] for row in self.cursor.fetchall()]
        except Exception as e:
            logger.error(f"获取表名失败: {str(e)}")
            return []
    
    def get_table_info(self, table_name: str) -> List[tuple]:
        """获取表结构信息"""
        try:
            self.cursor.execute(f"DESCRIBE {table_name}")
            return self.cursor.fetchall()
        except Exception as e:
            logger.error(f"获取表结构失败: {str(e)}")
            return []
    
    def save_common_symbols(self, symbols: List[str]):
        """保存共同交易对"""
        try:
            if not self.conn or not self.conn.is_connected():
                self.get_connection()
            
            # 清空旧数据
            self.cursor.execute("DELETE FROM common_symbols")
            
            # 插入新数据
            for symbol in symbols:
                self.cursor.execute(
                    "INSERT INTO common_symbols (symbol) VALUES (%s)",
                    (symbol,)
                )
            
            # 记录更新历史
            self.cursor.execute(
                "INSERT INTO update_history (table_name, action) VALUES (%s, %s)",
                ('common_symbols', 'update')
            )
            
            self.conn.commit()
            logger.info(f"成功保存 {len(symbols)} 个共同交易对")
            
        except Exception as e:
            logger.error(f"保存共同交易对失败: {str(e)}")
            raise
    
    def get_common_symbols(self) -> List[str]:
        """获取所有共同交易对"""
        try:
            # 使用已建立的连接
            if not self.conn or not self.conn.is_connected():
                self.get_connection()
                
            self.cursor.execute("SELECT symbol FROM common_symbols")
            results = self.cursor.fetchall()
            logger.info(f"从数据库查询到 {len(results)} 条记录")
            
            # 确保返回的是字符串列表
            symbols = [row['symbol'] for row in results]
            logger.info(f"解析后的交易对列表: {symbols}")
            return symbols
            
        except Exception as e:
            logger.error(f"获取共同交易对失败: {str(e)}")
            return []
    
    def get_symbol_count(self) -> int:
        """获取共同交易对数量"""
        try:
            if not self.conn or not self.conn.is_connected():
                self.get_connection()
                
            self.cursor.execute('SELECT COUNT(*) FROM common_symbols')
            result = self.cursor.fetchone()
            return result['COUNT(*)'] if result else 0
        except Exception as e:
            logger.error(f"获取交易对数量失败: {str(e)}")
            return 0
    
    def get_last_update_time(self) -> Optional[float]:
        """获取上次更新的时间戳（秒）"""
        try:
            if not self.conn or not self.conn.is_connected():
                self.get_connection()
                
            self.cursor.execute("SELECT MAX(timestamp) FROM update_history WHERE table_name = 'common_symbols' AND action = 'update'")
            result = self.cursor.fetchone()
            
            if result and result['MAX(timestamp)']:
                return result['MAX(timestamp)'].timestamp()
            return None
            
        except Exception as e:
            logger.error(f"获取上次更新时间戳失败: {str(e)}")
            return None
    
    def save_arbitrage_order(self, order: Dict[str, Any]):
        """保存套利订单"""
        try:
            if not self.conn or not self.conn.is_connected():
                self.get_connection()
            
            self.cursor.execute('''
                INSERT INTO arbitrage_orders (
                    symbol, buy_exchange, sell_exchange, buy_price, sell_price,
                    amount, profit, status
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
            ''', (
                order['symbol'], order['buy_exchange'], order['sell_exchange'],
                order['buy_price'], order['sell_price'], order['amount'],
                order['profit'], order['status']
            ))
            
            self.conn.commit()
            logger.info(f"成功保存套利订单: {order['symbol']}")
            
        except Exception as e:
            logger.error(f"保存套利订单失败: {str(e)}")
            raise
    
    def update_order_status(self, order_id: int, status: str):
        """更新订单状态"""
        try:
            if not self.conn or not self.conn.is_connected():
                self.get_connection()
            
            self.cursor.execute('''
                UPDATE arbitrage_orders 
                SET status = %s, updated_at = CURRENT_TIMESTAMP
                WHERE id = %s
            ''', (status, order_id))
            
            self.conn.commit()
            logger.info(f"成功更新订单状态: {order_id} -> {status}")
            
        except Exception as e:
            logger.error(f"更新订单状态失败: {str(e)}")
            raise
    
    def get_active_orders(self) -> List[Dict[str, Any]]:
        """获取所有活跃订单"""
        try:
            if not self.conn or not self.conn.is_connected():
                self.get_connection()
            
            self.cursor.execute('''
                SELECT * FROM arbitrage_orders 
                WHERE status = 'active'
                ORDER BY created_at DESC
            ''')
            
            return self.cursor.fetchall()
        except Exception as e:
            logger.error(f"获取活跃订单失败: {str(e)}")
            return []
    
    def get_closed_orders(self, limit: int = 100) -> List[Dict[str, Any]]:
        """获取最近关闭的订单"""
        try:
            if not self.conn or not self.conn.is_connected():
                self.get_connection()
            
            self.cursor.execute('''
                SELECT * FROM arbitrage_orders 
                WHERE status = 'closed'
                ORDER BY updated_at DESC
                LIMIT %s
            ''', (limit,))
            
            return self.cursor.fetchall()
        except Exception as e:
            logger.error(f"获取关闭订单失败: {str(e)}")
            return []
    
    def get_orders_by_symbol(self, symbol: str) -> List[Dict]:
        """获取特定交易对的订单"""
        try:
            if not self.conn or not self.conn.is_connected():
                self.get_connection()
            
            self.cursor.execute('''
                SELECT * FROM arbitrage_orders 
                WHERE symbol = %s
                ORDER BY created_at DESC
            ''', (symbol,))
            
            return self.cursor.fetchall()
        except Exception as e:
            logger.error(f"获取订单失败: {str(e)}")
            return []
    
    def get_total_profit(self) -> float:
        """计算总利润"""
        try:
            if not self.conn or not self.conn.is_connected():
                self.get_connection()
            
            self.cursor.execute('''
                SELECT SUM(profit) FROM arbitrage_orders 
                WHERE status = 'closed'
            ''')
            
            result = self.cursor.fetchone()
            return float(result['SUM(profit)']) if result['SUM(profit)'] is not None else 0.0
        except Exception as e:
            logger.error(f"计算总利润失败: {str(e)}")
            return 0.0
    
    def __del__(self):
        """析构函数"""
        try:
            if hasattr(self, 'conn') and self.conn is not None:
                self.close()
        except Exception as e:
            logger.error(f"关闭数据库连接时出错: {str(e)}") 